package com.uchedao.vehicledetection.view.carlist.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.uchedao.vehicledetection.R;
import com.uchedao.vehicledetection.view.carlist.Entity.CarBrand;
import com.uchedao.vehicledetection.view.carlist.Entity.CarModel;
import com.uchedao.vehicledetection.view.carlist.Entity.CarSeries;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;

/**
 * Created by ZhangZhanghua on 2015/8/24 0024.
 */
public class CarDbHelper extends SQLiteOpenHelper {
    public static final String KEY_ID = "id";
    public static final String KEY_FULL_SPELL = "full_spell";
    public static final String KEY_TEXT = "text";
    // "id": "53",
    // "full_spell": "dazhong",
    // "text": "大众",
    public static final String KEY_BRAND_ID = "brand_id";
    // "id": "380",
    // "brand_id": "53",
    // "full_spell": "pasate",
    // "text": "\u5e15\u8428\u7279",
    public static final String KEY_SERIES_ID = "series_id";
    public static final String KEY_MODEL_YEAR = "model_year";
    // "id": "337905",
    // "model_year": "2015",
    // "text":

    private static final String DB_NAME = "uchedao";

    public static final String TABLE_NAME_BRAND = "car_brand";

    public static final String TABLE_NAME_SERIES = "car_series";

    public static final String TABLE_NAME_MODEL = "car_model";

    private static final int VER_INT = 2;

    // 品牌
    private static final String BRAND_TABLE_SQL_CREATOR = "CREATE TABLE IF NOT EXISTS [" + TABLE_NAME_BRAND + "] ( ["
            + KEY_ID + "] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [" + KEY_FULL_SPELL + "] VARCHAR(32), ["
            + KEY_TEXT + "] VARCHAR(32)); ";

    // 系列
    private static final String SERIES_TABLE_SQL_CREATOR = "CREATE TABLE IF NOT EXISTS [" + TABLE_NAME_SERIES + "] ( ["
            + KEY_ID + "] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [" + KEY_BRAND_ID + "] INTEGER, ["
            + KEY_FULL_SPELL + "] VARCHAR(32), [" + KEY_TEXT + "] VARCHAR(32)); ";

    // 型号
    private static final String MODEL_TABLE_SQL_CREATOR = "CREATE TABLE IF NOT EXISTS [" + TABLE_NAME_MODEL + "] ( ["
            + KEY_ID + "] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [" + KEY_SERIES_ID + "] INTEGER,["
            + KEY_MODEL_YEAR + "] VARCHAR(32), [" + KEY_TEXT + "] String); ";

    private SQLiteDatabase db;

    private static CarDbHelper carDbHelper;

    public static CarDbHelper getInstance(Context context) {
        if (carDbHelper == null)
            carDbHelper = new CarDbHelper(context);
        return carDbHelper;
    }

    public CarDbHelper(Context context) {
        super(context, DB_NAME, null, VER_INT);
        db = getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        this.db = db;
        db.execSQL(BRAND_TABLE_SQL_CREATOR);
        db.execSQL(SERIES_TABLE_SQL_CREATOR);
        db.execSQL(MODEL_TABLE_SQL_CREATOR);
    }

    public SQLiteDatabase getDb() {
        return db;
    }

    /**
     * 插入品牌记录
     */
    public void insertBrand(int id, String name, String spell) {
        try {
            ContentValues values = new ContentValues();
            values.put(KEY_ID, id);
            values.put(KEY_TEXT, name);
            values.put(KEY_FULL_SPELL, spell);
            if (db == null)
                db = getWritableDatabase();
            if (db.isOpen())
                db.insert(TABLE_NAME_BRAND, null, values);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 插入系列记录
     */
    public void insertSeries(int id, int brandId, String name, String spell) {
        try {
            ContentValues values = new ContentValues();
            values.put(KEY_ID, id);
            values.put(KEY_BRAND_ID, brandId);
            values.put(KEY_TEXT, name);
            values.put(KEY_FULL_SPELL, spell);
            if (db == null)
                db = getWritableDatabase();
            if (db.isOpen())
                db.insert(TABLE_NAME_SERIES, null, values);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 插入车型记录
     */
    public void insertModel(int id, int seriesId, String name, String modelYear) {
        try {
            ContentValues values = new ContentValues();
            values.put(KEY_ID, id);
            values.put(KEY_SERIES_ID, seriesId);
            values.put(KEY_TEXT, name);
            values.put(KEY_MODEL_YEAR, modelYear);
            if (db == null)
                db = getWritableDatabase();
            if (db.isOpen())
                db.insert(TABLE_NAME_MODEL, null, values);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 记录是否存在
     *
     * @param tableName 表名
     * @param id        id
     * @return
     */
    public boolean hasRecord(String tableName, int id) {
        Cursor c = queryId(tableName, id);
        if (c == null)
            return false;
        boolean flag = c.getCount() > 0;
        c.close();
        c = null;
        return flag;
    }

    /**
     * 查询id记录是否存在
     *
     * @param tableName 表名
     * @param id        id
     * @return
     */
    public Cursor queryId(String tableName, int id) {
        if (db == null)
            db = getWritableDatabase();
        if (!db.isOpen())
            return null;
        return db.rawQuery("select * from " + tableName + " where " + KEY_ID + "=?",
                new String[]{String.valueOf(id)});
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void rawQuery(String sql, String[] selectionArgs) {
        db.rawQuery(sql, selectionArgs);
    }

    public void beginTransaction() {
        db.beginTransaction();
    }

    public void setTransactionSuccessful() {
        db.setTransactionSuccessful();
    }

    public void endTransaction() {
        db.endTransaction();
    }

    /**
     * 删除所有表
     */
    public void deleteAllTable() {
        db.delete(TABLE_NAME_BRAND, null, null);
        db.delete(TABLE_NAME_SERIES, null, null);
        db.delete(TABLE_NAME_MODEL, null, null);
    }


    /**
     * 得到汽车品牌列表
     *
     * @param cxt
     * @return
     */
    public static ArrayList<CarBrand> getCarBrand(Context cxt) {
        ArrayList<CarBrand> array = new ArrayList<CarBrand>();
        Cursor c = getInstance(cxt).getWritableDatabase().rawQuery(CarBrand.SEARCH_ALL_BRIND, null);
        CarBrand carBrand = null;
        if (c != null) {
            c.moveToFirst();
            while (!c.isAfterLast()) {
                carBrand = new CarBrand();
                carBrand.setId(c.getInt(0));
                carBrand.setName(c.getString(2));
                carBrand.setSpell(c.getString(1));

                array.add(carBrand);
                c.moveToNext();
            }
            c.close();
            c = null;
        }
        return array;
    }

    /**
     * 得到车系列表
     *
     * @param cxt
     * @return
     */
    public static ArrayList<CarSeries> getCarSeries(Context cxt, int brandId) {
        ArrayList<CarSeries> array = new ArrayList<CarSeries>();
        Cursor c = getInstance(cxt).getWritableDatabase().rawQuery(CarSeries.SEARCH_SERIES_BY_BRANDE_ID,
                new String[]{String.valueOf(brandId)});
        CarSeries carSeries = null;
        if (c != null) {
            c.moveToFirst();
            while (!c.isAfterLast()) {
                carSeries = new CarSeries();
                carSeries.setId(c.getInt(0));
                carSeries.setBrandId(c.getInt(1));
                carSeries.setName(c.getString(3));
                carSeries.setSpell(c.getString(2));
                array.add(carSeries);
                c.moveToNext();
            }
            c.close();
            c = null;
        }
        return array;
    }

    /**
     * 得到车型列表
     *
     * @param cxt
     * @return
     */
    public static ArrayList<CarModel> getCarModel(Context cxt, int seriesId) {
        ArrayList<CarModel> array = new ArrayList<CarModel>();
        Cursor c = getInstance(cxt).getWritableDatabase().rawQuery(CarModel.SELECT_MODEL,
                new String[]{String.valueOf(seriesId)});
        CarModel carModel = null;
        if (c != null) {
            c.moveToFirst();
            while (!c.isAfterLast()) {
                carModel = new CarModel();
                carModel.setId(c.getInt(0));
                carModel.setSeriesId(c.getInt(1));
                carModel.setModelYear(c.getString(2));
                carModel.setName(c.getString(3));
                array.add(carModel);
                c.moveToNext();
            }
            c.close();
            c = null;
        }
        return array;
    }

    /**
     * 导入数据库文件
     *
     * @param context
     * @return
     */
    public static boolean importDatabase(Context context) {

        boolean flag = true;
        // 存放数据库的目录
        String dirPath = "/data/data/" + context.getPackageName() + "/databases";
        File dir = new File(dirPath);
        if (!dir.exists()) {
            if (!dir.mkdir())
                return false;
        }
        if (!flag)
            return false;
        // 数据库文件
        File file = new File(dir, DB_NAME);

        if (file.exists()) {
//            if (!AppInfoManager.verUpdate) // 若版本没更新
//                return true;

            if (!file.delete()) {
                return false;
            }
        }
        try {
            if (!file.createNewFile()) {
                return false;
            }
            // 加载需要导入的数据库
            InputStream is = context.getResources().openRawResource(R.raw.ycp);
            FileOutputStream fos = new FileOutputStream(file);
            byte[] buffere = new byte[is.available()];
            is.read(buffere);
            fos.write(buffere);
            is.close();
            fos.close();
            return true;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return false;
    }
}
